Imports System.Data.SqlClient

Public Class Nomenclador : Implements IBusquedaFiltrado

    Private codigo As Integer
    Private nombre As String
    Private porcentaje As String
    Private precio As Double
    Private descripTipo As String
    Private valorNormal As String
    Private fechaVigDesde As Date
    Private fechaVigHasta As Date
    Private idEstudio As Integer


    Public Sub New()
        codigo = 0
        nombre = ""
        PorcentajeCobertura = ""
        precio = 0
        descripTipo = ""
        valorNormal = ""
        fechaVigDesde = Now
        fechaVigHasta = Now
        idEstudio = 0

    End Sub
    Public Property estu() As Integer
        Get
            Return idEstudio
        End Get
        Set(ByVal value As Integer)
            idEstudio = value
        End Set
    End Property
    Public Property CodigoAnalisis() As Integer
        Get
            Return Me.codigo
        End Get
        Set(ByVal value As Integer)
            Me.codigo = value
        End Set
    End Property

    Public Property NombreAnalisis() As String
        Get
            Return Me.nombre
        End Get
        Set(ByVal value As String)
            Me.nombre = value
        End Set
    End Property

    Public Property PorcentajeCobertura() As String
        Get
            Return Me.porcentaje
        End Get
        Set(ByVal value As String)
            Me.porcentaje = value
        End Set
    End Property

    Public Property PrecioNomenclador() As Double
        Get
            Return Me.precio
        End Get
        Set(ByVal value As Double)
            Me.precio = value
        End Set
    End Property
    Public Property descripcionTipo() As String
        Get
            Return Me.descripTipo
        End Get
        Set(ByVal value As String)
            Me.descripTipo = value
        End Set
    End Property
    Public Property ValorReferencia() As String
        Get
            Return Me.valorNormal
        End Get
        Set(ByVal value As String)
            Me.valorNormal = value
        End Set
    End Property
    Public Property fechaVigenciaD() As Date
        Get
            Return Me.fechaVigDesde
        End Get
        Set(ByVal value As Date)
            Me.fechaVigDesde = value
        End Set
    End Property
    Public Property fechaVigenciaH() As Date
        Get
            Return Me.fechaVigHasta
        End Get
        Set(ByVal value As Date)
            Me.fechaVigHasta = value
        End Set
    End Property

    Public Function Agregar(ByRef con As ConexionBD) As Boolean Implements IBusquedaFiltrado.Agregar
        Dim sql As String = ""
        Dim comando As SqlCommand = Nothing

        sql = "INSERT INTO NOMENCLADOR(IDANALISIS,NOMBRE,PORCCOBERTURA,PRECIO) VALUES({0},'{1}',{2},{3})"
        sql = String.Format(sql, Me.codigo, Me.nombre, Me.porcentaje, Me.precio, Me.descripTipo, Me.valorNormal, Me.fechaVigDesde, Me.fechaVigHasta)

        comando = New SqlCommand(sql, con.ConexionBase)
        If comando.ExecuteNonQuery() > 0 Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Function Buscar(ByVal id As Integer, ByRef con As ConexionBD) As Boolean Implements IBusquedaFiltrado.Buscar
        Dim sql As String = ""
        Dim comando As SqlCommand = Nothing
        Dim lResp As Boolean = False
        Dim rsNomenclador As SqlDataReader = Nothing

        sql = String.Format("SELECT * FROM NOMENCLADOR WHERE IDANALISIS ={0}", id)
        comando = New SqlCommand(sql, con.ConexionBase)
        rsNomenclador = comando.ExecuteReader()

        If rsNomenclador.Read() Then
            lResp = True
            Me.codigo = rsNomenclador("idAnalisis")
            Me.nombre = rsNomenclador("nombre")
            Me.porcentaje = rsNomenclador("porccobertura")
            Me.precio = rsNomenclador("precio")
            Me.descripTipo = rsNomenclador("descripTipo")
            Me.valorNormal = rsNomenclador("valorNormal")
            Me.fechaVigDesde = rsNomenclador("FechaDesde")
            Me.fechaVigHasta = rsNomenclador("FechaHasta")
        Else
            lResp = False
        End If

        rsNomenclador.Close()
        Return lResp

    End Function

    Public Function Eliminar(ByVal id As Integer, ByRef con As ConexionBD) As Boolean Implements IBusquedaFiltrado.Eliminar
        Dim sql As String = ""
        Dim comando As SqlCommand = Nothing

        sql = String.Format("DELETE FROM NOMENCLADOR WHERE IDANALISIS={0}", id)
        comando = New SqlCommand(sql, con.ConexionBase)
        If comando.ExecuteNonQuery() > 0 Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Function Modificar(ByRef con As ConexionBD) As Boolean Implements IBusquedaFiltrado.Modificar
        Dim sql As String = ""
        Dim comando As SqlCommand = Nothing

        sql = "UPDATE NOMENCLADOR " + _
              " SET NOMBRE = '{0}',PORCCOBERTURA = {1}, PRECIO={2}" + _
              " WHERE IDANALISIS = {4}"
        sql = String.Format(sql, Me.nombre, Me.porcentaje, Me.precio, Me.codigo)
        comando = New SqlCommand(sql, con.ConexionBase)
        If comando.ExecuteNonQuery() > 0 Then
            Return True
        Else
            Return False
        End If


    End Function

    Public Function BuscarDatos(ByVal id As Integer, ByRef con As ConexionBD) As DataSet

        Dim sql As String = ""
        Dim adapter As SqlDataAdapter = Nothing
        Dim datos As DataSet = New DataSet()

        sql = String.Format("SELECT N.* FROM ESTUDIOXANALISIS E,NOMENCLADOR N WHERE E.IDANALISIS=N.IDANALISIS AND E.IDESTUDIO ={0}", id)
        adapter = New SqlDataAdapter(sql, con.ConexionBase)
        adapter.Fill(datos, "nomenclador")


        Return datos
    End Function
    Public Function BuscarId(ByVal id As Integer, ByRef con As ConexionBD) As DataSet

        Dim sql As String = ""
        Dim adapter As SqlDataAdapter = Nothing
        Dim datos As DataSet = New DataSet()

        sql = String.Format("SELECT * FROM NOMENCLADOR WHERE IDANALISIS={0}", id)
        adapter = New SqlDataAdapter(sql, con.ConexionBase)
        adapter.Fill(datos, "nomenclador")


        Return datos
    End Function
End Class
